<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 *
 * @abstract 用户日志
 * @author 张伟
 *
 */
class SqlLog extends MY_Controller {

    function __construct() {
        parent::__construct();
    }

    /**
     * 用户操作日志
     */
    public function index(){
        $pagesize = 15;
        $page = max(1, intval($this->input->get_post('page')));
        //用户
        $users = $this->db->select('id,name')->from('user')
                    //->where('available',1)
                    ->order_by('name asc,id asc')
                    ->get()->result_array();
        //条件
        $where = array();
        $where_in = array();
        $search = array();
        //------------------用户id搜索
        $user_id = intval($this->input->get_post('user_id'));
        if($user_id > 0){
            $where['sl.user_id'] = $user_id;
            $search['user_id'] = $user_id;
        }

        $order_num = trim($this->input->get_post('order_num'));
        $order_num_multi = trim($this->input->get_post('order_num_multi'));
        //------------------普通单维修单号搜索
        if($order_num){
            $log_ids = [0];
            $order = $this->db->select('id')->from('mt_order')->where('order_num', $order_num)->get()->row_array();
            $details = $this->db->select('sql_log_id')
                ->from('mt_sql_log_detail')
                ->where('tbid', $order['id'])
                ->where('tbname', 'mt_order')
                ->get()->result_array();
            foreach ($details as $k => $v) {
                array_push($log_ids, $v['sql_log_id']);
            }
            $products = $this->db->select('id')->from('mt_product')->where('oid', $order['id'])->get()->result_array();
            $product_ids = [0];
            foreach ($products as $k => $v) {
                array_push($product_ids, $v['id']);
            }

            $details = $this->db->select('sql_log_id')
                ->from('mt_sql_log_detail')
                ->where_in('tbid', $product_ids)
                ->where('tbname', 'mt_product')
                ->get()->result_array();
            foreach ($details as $k => $v) {
                array_push($log_ids, $v['sql_log_id']);
            }

            $where_in['sl.id'] = $log_ids;

            $search['order_num'] = $order_num;
        }
        //------------------批量单维修单号搜索
        else if($order_num_multi){
            $log_ids = [0];
            $order = $this->db->select('id')->from('mt_multi_orders')->where('order_num', $order_num_multi)->get()->row_array();
            $details = $this->db->select('sql_log_id')
                ->from('mt_sql_log_detail')
                ->where('tbid', $order['id'])
                ->where('tbname', 'mt_multi_orders')
                ->get()->result_array();
            foreach ($details as $k => $v) {
                array_push($log_ids, $v['sql_log_id']);
            }
            $products = $this->db->select('id')->from('mt_multi_products')->where('oid', $order['id'])->get()->result_array();
            $product_ids = [0];
            foreach ($products as $k => $v) {
                array_push($product_ids, $v['id']);
            }

            $details = $this->db->select('sql_log_id')
                ->from('mt_sql_log_detail')
                ->where_in('tbid', $product_ids)
                ->where('tbname', 'mt_multi_products')
                ->get()->result_array();
            foreach ($details as $k => $v) {
                array_push($log_ids, $v['sql_log_id']);
            }

            $where_in['sl.id'] = $log_ids;

            $search['order_num_multi'] = $order_num_multi;
        }

        //列表
        $query = $this->db
            ->select('sl.id,sl.user_id,sl.desc,sl.create_at,sl.ip,u.name user_name')
            ->from('mt_sql_log sl')
            ->join('user u','sl.user_id=u.id','left')
            ->where($where);
        foreach ($where_in as $k => $v) {
            $query->where_in($k, $v);
        }
        $list = $query->order_by('sl.id desc')
            ->limit($pagesize, ($page - 1)*$pagesize)
            ->get()
            ->result_array();
        foreach ($list as $k => &$v) {
            $v['create_at'] = date('Y-m-d H:i:s', $v['create_at'] + 8*3600);//以中国时间显示
            $v['ip'] = long2ip($v['ip']);
            $v['user_name'] = $v['user_id'] > 0 ? $v['user_name'] : 'system';
        }
        //统计数量
        $sql = $this->db->last_query();//echo $sql;
        $sql = substr($sql, 0, stripos($sql, 'ORDER BY'));
        $sql = preg_replace('/(SELECT).*?(FROM.*)/is', '$1 count(*) `rows` $2', $sql);
        $query = $this->db->query($sql);
        $row = $query->row_array();
        $total_rows = (int)$row['rows']; //总数量
        $pagecount = ceil($total_rows / $pagesize); //总页数

        //分页
        $this->load->library('pagination');
        $config['page_query_string'] = TRUE;
        $config['base_url'] = blang_url('users/SqlLog/index?'.http_build_query($search));
        $config['total_rows'] = $total_rows;
        $config['per_page'] = $pagesize;
        $config['uri_segment'] = config_item('enable_lang') ? 5 : 4;
        $this->pagination->initialize($config);


        $this->load->view('defaults/sql_log', array(
            'users' => $users,
            'list' => $list,
            'search' => $search,
            'show_pages' => $this->pagination->create_links(),
            'show_params' => "总共{$config['total_rows']}条记录   {$config['per_page']}条/页   当前第{$page}/{$pagecount}页"
        ));
        $this->load->view('defaults/common/footer');
    }

    public function detail(){
        header('content-type: text/html; charset=utf-8;');
        $this->output->set_output('');
        $log_id = intval($this->input->get_post('id'));
        $log = $this->db->where('id', $log_id)->from('mt_sql_log')->get()->row_array();
        $log['create_at'] = date('Y-m-d H:i:s', $log['create_at'] + 8*3600);
        $log['ip'] = long2ip($log['ip']);
        if ($log['user_id'] > 0) {
            $user = $this->db->select('name')->from('user')
                        ->where('id', $log['user_id'])
                        ->get()->row_array();
            $log['user_name'] = $user['name'];
        }else{
            $log['user_name'] = 'system';
        }
        
        $detail = $this->db
                    ->select('id,sql_log_id,tbid,tbname,type,old,`new`,sql')
                    ->where('sql_log_id', $log_id)
                    ->from('mt_sql_log_detail')
                    ->get()->result_array();
        foreach ($detail as $k => $v) {
            $detail[$k]['old'] = json_decode($v['old'], true);
            $detail[$k]['new'] = json_decode($v['new'], true);
        }
        $this->load->view('defaults/sql_log_detail', [
            'log' => $log,
            'detail' => $detail
        ], '成功',true);
    }
}
